1 Public Class FrmPHYSICALCOUNT_ADD
2 Dim enterX As Boolean
3 Dim Physical_ID As Integer
4
5 Private Sub FrmPHYSICALCOUNT_ADD_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
6 Me.Text = "Physical Details"
7 End Sub
8
9 Private Sub FrmPHYSICALCOUNT_ADD_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
10 Dim x As Integer
11 lstcur_stocks.Visible = False
12 lstphysical.Items.Clear()
13 txtqtyi.Text = 0
14 txtID.Text = 0
15 txtqty.Text = 0
16 txtproductname.Text = ""
17 txtdesc.Text = ""
18 txtproductname.Select()
19 txtverified.Text = _USER.Value
20 cmdaddphysical.Enabled = False
21
22 lstphysical.Items.Clear()
23 If Split(Me.Text, " - ")(1) = "Edit" Then
24 Physical_ID = globalID
25 sqlSTR = "SELECT * , * " & _
26 "FROM TBL_Physical_Count_Details " & _
27 "INNER JOIN TBL_Category_Item_File ON TBL_Physical_Count_Details.Item_ID = TBL_Category_Item_File.Item_ID " & _
28 "WHERE P_ID =" & Physical_ID
29 ExecuteSQLQuery(sqlSTR)
30 If sqlDT.Rows.Count > 0 Then
31 With lstphysical
32 For x = 0 To sqlDT.Rows.Count - 1
33 .Items.Add(sqlDT.Rows(x)("Item_ID"))
34 .Items(.Items.Count - 1).SubItems.Add(R_Change(sqlDT.Rows(x)("Item_Name")))
35 .Items(.Items.Count - 1).SubItems.Add(R_Change(sqlDT.Rows(x)("Item_Description")))
36 .Items(.Items.Count - 1).SubItems.Add(R_Change(sqlDT.Rows(x)("P_REMARKS")))
37 .Items(.Items.Count - 1).SubItems.Add(sqlDT.Rows(x)("P_Counts"))
38 .Items(.Items.Count - 1).SubItems.Add(sqlDT.Rows(x)("Total_QTY"))
39 Next
40 End With
41 End If
42 End If
43 End Sub
44
45 Private Sub txtproductname_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtproductname.KeyDown
46 If e.KeyCode = 40 And lstcur_stocks.Items.Count > 0 Then
47 lstcur_stocks.Focus()
48 lstcur_stocks.Items(0).Selected = True
49 End If
50 End Sub
51
52
53 Private Sub txtproductname_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtproductname.TextChanged
54 If Len(txtproductname.Text) > 0 And Not enterX Then
55 lstcur_stocks.Items.Clear()
56 lstcur_stocks.Top = (txtproductname.Top + txtproductname.Height)
57 lstcur_stocks.Left = txtproductname.Left
58
59
60 sqlSTR = "SELECT TBL_category_item_file.item_id AS 'ID', TBL_Category_Item_File.Item_Name as 'Name', TBL_Category_Item_File.Item_Description AS 'Description', TBL_Category_Item_File.Unit_Measure, TBL_Category_Item_File.Item_Price, TBL_Stocks_Balances.Item_QTY " & _
61 "FROM TBL_category_item_file INNER JOIN TBL_Stocks_Balances ON TBL_Category_Item_File.Item_ID = TBL_Stocks_Balances.Item_ID " & _
62 "WHERE LEFT(TBL_Category_Item_File.Item_Name," & Len(R_eplace(txtproductname.Text)) & ") ='" & R_eplace(txtproductname.Text) & "'" _
63 & "ORDER BY TBL_Category_Item_File.Item_name"
64 ExecuteSQLQuery(sqlSTR)
65 '---
66 For x = 0 To sqlDT.Rows.Count - 1
67 ' lstItems.Items.Add(sqlDT.Rows(i)("Item_ID"))
68 ' lstItems.Items((lstItems.Items.Count - 1)).SubItems.Add(sqlDT.Rows(i)("Item_Name"))
69 lstcur_stocks.Items.Add(sqlDT.Rows(x)("ID"))
70 lstcur_stocks.Items((lstcur_stocks.Items.Count - 1)).SubItems.Add(R_Change(sqlDT.Rows(x)("Name")))
71 lstcur_stocks.Items((lstcur_stocks.Items.Count - 1)).SubItems.Add(sqlDT.Rows(x)("Description"))
72 lstcur_stocks.Items((lstcur_stocks.Items.Count - 1)).SubItems.Add(sqlDT.Rows(x)("Item_QTY"))
73 Next
74 lstcur_stocks.Visible = True
75 '---
76 enterX = False
77 Else
78 lstcur_stocks.Visible = False
79 enterX = False
80 End If
81 End Sub
82
83 Private Sub cmdclose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdclose.Click
84 Me.Close()
85 End Sub
86
87 Private Sub lstcur_stocks_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles lstcur_stocks.KeyDown
88 If e.KeyCode = 13 Then
89 enterX = True
90 txtID.Text = lstcur_stocks.FocusedItem.Text
91 txtproductname.Text = lstcur_stocks.FocusedItem.SubItems(1).Text
92 txtdesc.Text = lstcur_stocks.FocusedItem.SubItems(2).Text
93 txtqtyi.Text = lstcur_stocks.FocusedItem.SubItems(3).Text
94 lstcur_stocks.Visible = False
95 txtqty.Focus()
96 ElseIf e.KeyCode = 8 Then
97 enterX = False
98 txtID.Text = 0
99 txtqtyi.Text = 0
100 txtproductname.Focus()
101 lstcur_stocks.Visible = False
102 End If
103 End Sub
104
105 Private Sub lstcur_stocks_MouseDoubleClick(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles lstcur_stocks.MouseDoubleClick
106 enterX = True
107 lstcur_stocks.Visible = False
108 txtID.Text = lstcur_stocks.FocusedItem.Text
109 txtproductname.Text = lstcur_stocks.FocusedItem.SubItems(1).Text
110 txtdesc.Text = lstcur_stocks.FocusedItem.SubItems(2).Text
111 txtqty.Text = lstcur_stocks.FocusedItem.SubItems(3).Text
112
113 End Sub
114
115 Private Sub lstcur_stocks_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lstcur_stocks.SelectedIndexChanged
116
117 End Sub
118
119 Private Sub txtqty_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtqty.TextChanged
120 If txtqty.Text = "" Then txtqty.Text = 0 And txtqty.Focus
121 txtqty.Text = str_Filter(txtqty, 48, 57, 0, 0)
122 If CDbl(txtqty.Text) > CDbl(txtqtyi.Text) Then
123 MsgBox("Physical quantity should not be greater than current quantity !!" & _
124 Chr(13) & "Physical Quantity : " & txtqty.Text & _
125 Chr(13) & "Current Quantity : " & txtqtyi.Text, MsgBoxStyle.Information, "Sales and Inventory")
126
127 txtqty.Text = 0
128 txtqty.Focus()
129
130 cmdaddphysical.Enabled = False
131 ElseIf CDbl(txtqty.Text) < CDbl(txtqtyi.Text) Then
132 txtremarks.Text = "Missing " & CDbl(txtqtyi.Text) - CDbl(txtqty.Text) & " item(s)"
133 cmdaddphysical.Enabled = True
134 If CDbl(txtqty.Text) = 0 Then
135 cmdaddphysical.Enabled = False
136 End If
137 ElseIf CDbl(txtqty.Text) = CDbl(txtqtyi.Text) Then
138 txtremarks.Text = ""
139 If CDbl(txtqty.Text) > 0 Then
140 cmdaddphysical.Enabled = True
141 End If
142 End If
143 End Sub
144
145 Private Sub cmdaddphysical_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdaddphysical.Click
146 Dim i, iTemp As Integer
147 Dim bReplace As Boolean
148 cmdaddphysical.Enabled = False
149 For i = 0 To lstphysical.Items.Count - 1
150 If lstphysical.Items(i).Text = txtID.Text Then
151 If MsgBox("Item is already exist, do you want to replace this ??", MsgBoxStyle.Information + MsgBoxStyle.YesNo) = MsgBoxResult.Yes Then
152 bReplace = True
153 iTemp = i
154 End If
155 End If
156 Next
157
158 With lstphysical
159 If bReplace = False Then
160 .Items.Add(txtID.Text)
161 .Items(.Items.Count - 1).SubItems.Add(txtproductname.Text)
162 .Items(.Items.Count - 1).SubItems.Add(txtdesc.Text)
163 .Items(.Items.Count - 1).SubItems.Add(txtremarks.Text)
164 .Items(.Items.Count - 1).SubItems.Add(txtqty.Text)
165 .Items(.Items.Count - 1).SubItems.Add(txtqtyi.Text)
166 Else
167 .Items(iTemp).SubItems(3).Text = txtremarks.Text
168 .Items(iTemp).SubItems(4).Text = txtqty.Text
169 End If
170
171 txtID.Text = 0
172 txtqtyi.Text = 0
173 txtqty.Text = 0
174 txtdesc.Text = ""
175 txtproductname.Text = ""
176 txtproductname.Select()
177 End With
178 End Sub
179
180 Private Sub cmdsave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdsave.Click
181 Dim x, y, Counter As Integer
182 Dim STOCK As Boolean
183 'Dim SQLStrx(0) As String
184 Dim Physical_to_Delete(0) As String
185 STOCK = False
186 If lstphysical.Items.Count > 0 Then
187 If Split(Me.Text, " - ")(1) = "Add" Then
188
189
190 sqlSTR = "INSERT INTO TBL_Physical_Count (p_Date, User_ID) VALUES ('" & Format(dtphysical.Value, "MM/dd/yyyy") & "', " & xUser_ID & ")"
191 ExecuteSQLQuery(sqlSTR)
192 sqlSTR = "SELECT * FROM TBL_Physical_Count ORDER BY P_ID DESC"
193 ExecuteSQLQuery(sqlSTR)
194 If sqlDT.Rows.Count > 0 Then
195 Physical_ID = sqlDT.Rows(0)("P_ID")
196 End If
197
198 'ADD NEW ITEMS
199 For x = 0 To lstphysical.Items.Count - 1
200 sqlSTR = "INSERT INTO TBL_Physical_Count_Details (P_ID, Item_ID, P_Counts, P_Remarks, Total_QTY) " & _
201 "VALUES(" & Physical_ID & ", " & _
202 lstphysical.Items(x).Text & ", " & _
203 lstphysical.Items(x).SubItems(4).Text & ", '" & _
204 lstphysical.Items(x).SubItems(3).Text & "', " & _
205 lstphysical.Items(x).SubItems(5).Text & ")"
206 ExecuteSQLQuery(sqlSTR)
207 Next
208 Audit_Trail(xUser_ID, TimeOfDay, "Add New Physical Count")
209 Else ' EDIT
210 '---
211 sqlSTR = "UPDATE TBL_Physical_Count SET p_Date ='" & Format(dtphysical.Value, "MM/dd/yyyy") & "', " _
212 & "User_ID =" & xUser_ID
213 ExecuteSQLQuery(sqlSTR)
214
215 'ADD NEW IF NOT EXIST IN THE LIST AND ITEMS ARE EDITED
216 For i = 0 To lstphysical.Items.Count - 1
217 sqlSTR = "SELECT * FROM TBL_Physical_Count_Details WHERE P_ID =" & Physical_ID & _
218 " AND Item_ID =" & lstphysical.Items(i).Text
219 'MsgBox("second")
220 ExecuteSQLQuery(sqlSTR)
221 If sqlDT.Rows.Count = 0 Then
222 sqlSTR = "INSERT INTO TBL_Physical_Count_Details (P_ID, Item_ID, P_Counts, P_Remarks, TOtal_QTY) " & _
223 "VALUES(" & Physical_ID & ", " & lstphysical.Items(i).Text & ", " & _
224 lstphysical.Items(i).SubItems(4).Text & ", '" & _
225 lstphysical.Items(i).SubItems(3).Text & "', " & _
226 lstphysical.Items(i).SubItems(5).Text & ")"
227 'MsgBox("Third")
228 ExecuteSQLQuery(sqlSTR)
229 Else 'EDIT MODE
230 sqlSTR = "UPDATE TBL_Physical_Count_Details SET P_Counts =" & lstphysical.Items(i).SubItems(4).Text & ", " _
231 & "P_Remarks='" & lstphysical.Items(i).SubItems(3).Text & "'" & _
232 " WHERE P_ID =" & Physical_ID & _
233 " AND Item_ID =" & lstphysical.Items(i).Text
234 ExecuteSQLQuery(sqlSTR)
235 End If
236 Next
237
238 '----
239 sqlSTR = "SELECT * FROM TBL_Physical_Count_Details WHERE P_ID =" & Physical_ID 'txtorderno.Text
240 'MsgBox("Fourth")
241 ExecuteSQLQuery(sqlSTR)
242 'IF item is delete in the list but still exist in the database
243 ' MsgBox(sqlDT.Rows.Count)
244 For x = 0 To sqlDT.Rows.Count - 1
245 STOCK = False
246 For Y = 0 To lstphysical.Items.Count - 1
247 'MsgBox(sqlDT.Rows(X)("Item_ID") & " ---- " & lstCurrentLoad.Items(Y).Text)
248 If lstphysical.Items(Y).Text = sqlDT.Rows(x)("Item_ID") Then
249 STOCK = True
250 End If
251 Next
252 If Not STOCK Then
253 'MsgBox("here stocks")
254 ReDim Preserve Physical_to_Delete(counter)
255 'MsgBox("Five")
256 Physical_to_Delete(Counter) = "DELETE FROM TBL_Physical_Count_Details WHERE P_ID =" & Physical_ID & " AND Item_ID =" & sqlDT.Rows(x)("Item_ID")
257 counter += 1
258 End If
259 Next
260
261 'DELETE ALL THE UNECCESSARY RECORDS
262 For x = 0 To UBound(Physical_to_Delete)
263 'MsgBox("Six")
264 If Len(Physical_to_Delete(x)) > 0 Then
265 ExecuteSQLQuery(Physical_to_Delete(x))
266 End If
267
268 Next
269 '----
270 Audit_Trail(xUser_ID, TimeOfDay, "Edit Physical Count")
271 End If
272 Else
273 MsgBox("Can't continue saving without details !!", MsgBoxStyle.Exclamation, "Sales and Inventory")
274 Exit Sub
275 End If
276
277 With FrmPhysicalCount
278 sqlSTR = "SELECT P_ID AS 'P_ID', P_Date as 'DATE', Lastname + ', ' + Firstname + ' ' + Middlename AS ' Username' " & _
279 "FROM TBL_Physical_Count " & _
280 "INNER JOIN TBL_Users ON TBL_Physical_Count.User_ID = TBL_Users.User_ID " & _
281 " WHERE P_Date >='" & Format(.dtfrom.Value, "MM/dd/yyyy") & "' AND P_Date <='" & Format(.dtto.Value, "MM/dd/yyyy") & "'"
282 FillListView(ExecuteSQLQuery(sqlSTR), .lstphysical, 0)
283 End With
284 MsgBox("Record saved !!", MsgBoxStyle.Information, "Sales and Inventory")
285 Me.Close()
286 End Sub
287
288 Private Sub cmdremove_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdremove.Click
289 If lstphysical.Items.Count > 0 Then
290 lstphysical.Focus()
291 lstphysical.Select()
292 If MsgBox("Do you want to remove this data ???", MsgBoxStyle.YesNo, "Sales and Inventory") = MsgBoxResult.Yes Then
293 lstphysical.FocusedItem.Remove()
294 End If
295 End If
296 End Sub
297 End Class
